﻿using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tauren.Business.Entity.Global;
using Tauren.Business.Entity.Model;
using Tauren.Business.Entity.Output;
using Tauren.Business.Infrastructure.Globals;

namespace Tauren.Business.DLL.TableInfoDLL
{
    public class TableInfoBusiness : Base.BaseDLL, ITableInfoBusiness
    {
        public TableInfoBusiness(IConnectionDLLBase _dLLBase) : base(_dLLBase)
        {
        }

        public bool CreateTbale(TableInfo info)
        {
            StringBuilder script = new StringBuilder();
            string foreignkey = "";
            if (!info.IsPrimary.Value)
            {
                foreignkey = " foreignkey bigint not null ,";
            }
            else
            {
                switch (info.GroupType.Value)
                {
                    case 2:
                        {
                            foreignkey += " orgid int ,"; if (info.IsFlow.Value) foreignkey += " empid int ,";
                            script.Append($@"INSERT INTO `fields`(`tablecode`, `fieldcode`, `fieldname`, `fieldenname`, `controlstype`, `controlsouces`, `fieldtype`, `selecttype`, `defaultvalue`, `fieldlength`, `isrequired`, `isinlay`, `enable`, `havedelete`, `precisions`, `sequence`, `creator`, `modifier`, `creatdate`, `modifdate`) VALUES ( '{info.TableCode}', 'empid', '员工id', 'Emp Id', 'employee', NULL, 9, 0, NULL, 0, b'0', b'1', b'1', b'0', 0, 0.1, 1, 1, NOW(), NOW());   ");
                            script.Append($@"INSERT INTO `fields`(`tablecode`, `fieldcode`, `fieldname`, `fieldenname`, `controlstype`, `controlsouces`, `fieldtype`, `selecttype`, `defaultvalue`, `fieldlength`, `isrequired`, `isinlay`, `enable`, `havedelete`, `precisions`, `sequence`, `creator`, `modifier`, `creatdate`, `modifdate`) VALUES ( '{info.TableCode}', 'orgid', '组织id', 'Organizational Id', 'organizational', NULL, 9, 0, NULL, 0, b'0', b'1', b'1', b'0', 0, 0.1, 1, 1, NOW(), NOW());   ");
                        }; break;
                    case 3:
                        {
                            foreignkey += " positionid int ,"; if (info.IsFlow.Value) foreignkey += " empid int ,";
                            script.Append($@"INSERT INTO `fields`(`tablecode`, `fieldcode`, `fieldname`, `fieldenname`, `controlstype`, `controlsouces`, `fieldtype`, `selecttype`, `defaultvalue`, `fieldlength`, `isrequired`, `isinlay`, `enable`, `havedelete`, `precisions`, `sequence`, `creator`, `modifier`, `creatdate`, `modifdate`) VALUES ( '{info.TableCode}', 'empid', '员工id', 'Emp Id', 'employee', NULL, 9, 0, NULL, 0, b'0', b'1', b'1', b'0', 0, 0.1, 1, 1, NOW(), NOW());   ");
                            script.Append($@"INSERT INTO `fields`(`tablecode`, `fieldcode`, `fieldname`, `fieldenname`, `controlstype`, `controlsouces`, `fieldtype`, `selecttype`, `defaultvalue`, `fieldlength`, `isrequired`, `isinlay`, `enable`, `havedelete`, `precisions`, `sequence`, `creator`, `modifier`, `creatdate`, `modifdate`) VALUES ( '{info.TableCode}', 'positionid', '职位id', 'Position Id', 'position', NULL, 9, 0, NULL, 0, b'0', b'1', b'1', b'0', 0, 0.1, 1, 1, NOW(), NOW());   ");
                        }; break;
                    default:
                        {
                            foreignkey += " empid int ,";
                            script.Append($@"INSERT INTO `fields`(`tablecode`, `fieldcode`, `fieldname`, `fieldenname`, `controlstype`, `controlsouces`, `fieldtype`, `selecttype`, `defaultvalue`, `fieldlength`, `isrequired`, `isinlay`, `enable`, `havedelete`, `precisions`, `sequence`, `creator`, `modifier`, `creatdate`, `modifdate`) VALUES ( '{info.TableCode}', 'empid', '员工id', 'Emp Id', 'employee', NULL, 9, 0, NULL, 0, b'0', b'1', b'1', b'0', 0, 0.1, 1, 1, NOW(), NOW());   ");
                        }
                        break;
                }
            }
            string instance = "";
            if (info.IsFlow.Value)
            {
                instance = " instanceid              bigint,";
                script.Append($@"create table {info.FlowTableCode}( autoId bigint not null, 
                            {foreignkey}                           
                            creator              int,
                            modifier             int,
                            creatdate            datetime,
                            modifdate            datetime,
                            status               int ,
                            primary key (autoId));   ");
            }
            script.Append($@"create table {info.TableCode}( autoId bigint not null, 
                            {foreignkey}
                            enable               bit,
                            sequence             double,
                            {instance}
                            creator              int,
                            modifier             int,
                            creatdate            datetime,
                            modifdate            datetime,
                            havedelete           bit,  
                            primary key (autoId));");
            return dbConnection.Execute(script.ToString()) >= 0;
        }

        public decimal GetSeq()
        {
            TableInfo TableInfo = this.dbConnection.QueryFirstOrDefault<TableInfo>("SELECT * FROM TableInfo WHERE havedelete=0  ORDER BY autoid DESC LIMIT 0,1");
            if (TableInfo is null) return 1;
            else return (TableInfo.Sequence is null ? 1 : TableInfo.Sequence.Value + 1);
        }

        public List<VwTableInfo> GetTableInfoByCode(string Code) => dbConnection.Query<VwTableInfo>("SELECT  cnname,enname,tablecode FROM TableInfo WHERE tableCode=@TableCode", new TableInfo() { TableCode = Code }).AsList();

        public bool IsPermissionCodeRepeat(long AutoId, string Code)
        {
            string sql = $"SELECT * FROM TableInfo WHERE tableCode='{Code}'";
            if (AutoId != 0)
            {
                sql = $"{sql} AND autoid != {AutoId}";
            }
            return dbConnection.Query<TableInfo>(sql).Any();
        }
        public TableInfo Table(string Code)
        {
            string sql = $"SELECT * FROM TableInfo WHERE tableCode='{Code}'";

            return dbConnection.QueryFirstOrDefault<TableInfo>(sql);
        }


        public List<PageTableInfoTable> Pages(QueryModel queryModel, out long Total)
        {
            Total = 0;
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $"   AND r.tablecode LIKE '%{queryModel.KeyWord}%' OR r.cnname LIKE '%{queryModel.KeyWord}%' OR r.enname LIKE '%{queryModel.KeyWord}%' ";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "sequence";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = queryModel.SortField.ToLower().Replace("dic_", "").Replace("dic", "").Trim();
            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    if (!string.IsNullOrEmpty(o.Value + ""))
                        where += $" AND r.{o.Label}!='{o.Value}'";
                });
            }
            if (queryModel.paramters.Any())
            {
                queryModel.paramters.ForEach(o =>
                {
                    if (o.Label.ToLower() == "isprimary")
                    {
                        if (!string.IsNullOrEmpty(o.Value + ""))
                            where += $" AND r.{o.Label} = {o.Value}  ";
                    }
                    else if (o.Label.ToLower() == "records")
                    {
                        if (!string.IsNullOrEmpty(o.Value + ""))
                            where += $" AND r.{o.Label} = {o.Value}    ";
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(o.Value + ""))
                            where += $" AND r.{o.Label} like '%{o.Value}%'  ";
                    }

                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND r.enable=1  ";
            }
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"SELECT count(r.AutoId) as cnt FROM TableInfo r WHERE r.havedelete !=1
                        {where}  ;");
            sql.Append(@$"SELECT r.autoid,r.cnname,r.enname,r.tablecode,r.grouptype,r.viewtype,r.viewtype,r.isprimary,r.pktable,r.issystem,r.isflow,r.records,r.`enable`,r.creator,r.creatdate,r.flowtablecode FROM TableInfo r 
                          WHERE r.havedelete !=1
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{queryModel.PageSize} ;");
            using (var reader = dbConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<PageTableInfoTable> pageModules = reader.Read<PageTableInfoTable>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                pageModules.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return pageModules;
            }
        }
    }
}
